﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using Bestlife.ERP.DataAccess;

/// <summary>
/// Summary description for D_Kandy
/// </summary>
/// 
namespace Bestlife.ERP.DataAccess
{
    public class D_Kandy
    {
        public static int status1;
        public static decimal QuantityDB;
        public static Boolean status;
        public static decimal lastid;

        public D_Kandy()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static int allissuapprove(List<Bestlife.ERP.Entities.E_Issue_Ap> grn, string user, string approve, int itemcount)
        {
            try
            {
                SqlDataReader rdR;
                string code;
                string proname;
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                foreach (Bestlife.ERP.Entities.E_Issue_Ap gn in grn)
                {

                    string sql1 = "UPDATE [Tbl_Iss_Request] SET [manger_status] ='" + approve + "',[Last_Modify] ='" + date + "'  WHERE  [Issue_ID] ='" + gn.Issu_ID1 + "' and [Sto_ID]='" + gn.Stor_ID + "'";
                    string sql = "UPDATE [Tbl_ISSUE] SET [status] ='" + approve + "',[Last_Modify] ='" + date + "'   WHERE Issue_ID='" + gn.Issu_ID1 + "' ";
                    string sql2 = "SELECT  [Code],[Pro_ID],[Pro_Name]  FROM [Tbl_Issue_Stock] where [IssuSto_ID]='" + gn.Stor_ID + "'";

                    Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);

                    rdR = Connection.ReadTable(sql2);
                    if (edit && rdR.Read())
                    {
                        proname = rdR["Pro_Name"].ToString();
                        code = rdR["Code"].ToString();
                        rdR.Close();


                        if (edit)
                        {
                            Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
                            if (edit1)
                            {
                                //string proid = Bestlife.ERP.DataAccess.D_receivedstock.getproid(proname);
                                Boolean ss = Bestlife.ERP.DataAccess.D_Kandy.addphystock(proname, gn.Qty, user, code, gn.Pro_id);
                                if (ss)
                                {
                                    decimal SK_Qty = Bestlife.ERP.DataAccess.D_Kandy.Sk_Stockcheck(gn.Pro_id, proname, code);
                                    if (SK_Qty >= gn.Qty)
                                    {
                                        Boolean result = Bestlife.ERP.DataAccess.D_Kandy.upd_SK_Stock(gn.Qty, SK_Qty, user, gn.Pro_id, code);
                                        if (result)
                                        {
                                            status1 = 1;
                                        }
                                        else
                                        {
                                            status1 = 2;
                                        }
                                    }
                                    else
                                    {
                                        status1 = 3;
                                    }
                                }
                                else
                                {
                                    status1 = 4;
                                }
                            }
                            else
                            {
                                status1 = 5;
                            }

                        }
                        else
                        {
                            status1 = 6;
                        }

                    }
                    else
                    {
                        status1 = 7;
                    }


                }
            }
            catch
            {
            }
            return status1;

        }

        public static Boolean addphystock(string proname, decimal qty, string user, string code, string proid)
        {
            Boolean upd2 = false;
            try
            {

                DateTime date = System.DateTime.Now;
                Decimal QuantityinDB = Bestlife.ERP.DataAccess.D_Kandy.ReturnissuStockQuantity(proid, proname, code);

                if (qty > 0)
                {
                    Decimal QuantityToDB = QuantityinDB + Decimal.Parse(qty.ToString());
                    if (QuantityToDB >= 0)
                    {

                        //string product = getproname(proname);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "INSERT INTO [Tbl_Kandy_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + proname + "','" + qty + "','" + user + "','" + date + "','" + proid + "','" + code + "')";
                        //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                        //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                    }
                    else
                    {
                        //print we have only QuantityinDB now !!!
                    }
                }
                else
                {
                    // sory we have nt any Product 
                }
            }
            catch
            {
            }
            return upd2;
        }


        public static Decimal ReturnissuStockQuantity(string pro_id, string proname, string code)
        {
            SqlDataReader rdR;


            try
            {
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                // string product = getproname(pro_id);
                String Ssql = "SELECT Kandy_Quantity from Tbl_Issue_Stock  where Pro_ID='" + pro_id + "' and Code='" + code + "' ";
                //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);


                rdR = Connection.ReadTable(Ssql);


                if (rdR.Read())
                {
                    QuantityDB = Decimal.Parse(rdR["Kandy_Quantity"].ToString());

                }
                rdR.Close();
            }
            catch (Exception)
            {

            }

            return QuantityDB;

        }



        public static Decimal Sk_Stockcheck(string pro_id, string proname, string code)
        {
            SqlDataReader rdR;


            try
            {
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                // string product = getproname(pro_id);
                String Ssql = "SELECT Qty_in_stock from Tbl_Stock  where Pro_ID='" + pro_id + "' and Code='" + code + "' ";
                //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);


                rdR = Connection.ReadTable(Ssql);


                if (rdR.Read())
                {
                    QuantityDB = Decimal.Parse(rdR["Qty_in_stock"].ToString());

                }
                rdR.Close();
            }
            catch (Exception)
            {

            }

            return QuantityDB;

        }

        public static Boolean upd_SK_Stock(decimal qty, decimal SK_stock, string user, string proid, string code)
        {
            try
            {


                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                decimal newstock = SK_stock - qty;

                string sql1 = "UPDATE [Tbl_Stock]   SET [Qty_in_stock] ='" + newstock + "', [Last_Modify]='" + date + "' WHERE [Pro_ID]='" + proid + "' and [Code]='" + code + "'";



                Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);

                if (edit1)
                {

                    status = true;
                }
                else
                {
                    status = false;
                }




            }
            catch
            {
            }
            return status;

        }

        public static int approveonebyone(Bestlife.ERP.Entities.E_Issue_Ap gn, string user, string approval, int itemcount)
        {
            try
            {
                SqlDataReader rdR;
                string code;
                string proname;
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                //foreach (Bestlife.ERP.Entities.E_GRN gn in grn)
                //{
                string sql = "UPDATE [Tbl_Iss_Request] SET [manger_status] ='" + approval + "',[Last_Modify] ='" + date + "'  WHERE  [Issue_ID] ='" + gn.Issu_ID1 + "' and [Sto_ID]='" + gn.Stor_ID + "'";

                string sql1 = "UPDATE [Tbl_ISSUE] SET [status] ='" + approval + "',[Last_Modify] ='" + date + "'   WHERE Issue_ID='" + gn.Issu_ID1 + "' ";

                string sql2 = "SELECT [Code],[Pro_ID],[Pro_name] FROM [Tbl_Issue_Stock]  where [IssuSto_ID]='" + gn.Stor_ID + "'";

                Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
                rdR = Connection.ReadTable(sql2);
                if (edit && rdR.Read())
                {
                    code = rdR["Code"].ToString();
                    proname = rdR["Pro_name"].ToString();
                    rdR.Close();



                    if (edit)
                    {
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                        if (edit1)
                        {
                            //string proid = Bestlife.ERP.DataAccess.D_receivedstock.getproid(proname);
                            Boolean ss = Bestlife.ERP.DataAccess.D_Kandy.addphystock(proname, gn.Qty, user, code, gn.Pro_id);
                            if (ss)
                            {
                                decimal SK_Qty = Bestlife.ERP.DataAccess.D_Kandy.Sk_Stockcheck(gn.Pro_id, proname, code);
                                if (SK_Qty >= gn.Qty)
                                {
                                    Boolean result = Bestlife.ERP.DataAccess.D_Kandy.upd_SK_Stock(gn.Qty, SK_Qty, user, gn.Pro_id, code);
                                    if (result)
                                    {
                                        status1 = 1;
                                    }
                                    else
                                    {
                                        status1 = 2;
                                    }
                                }
                                else
                                {
                                    status1 = 3;

                                }
                            }
                            else
                            {
                                status1 = 4;
                            }
                        }
                        else
                        {
                            status1 = 5;
                        }

                    }
                    else
                    {
                        status1 = 6;
                    }

                }
                else
                {
                    status1 = 7;
                }


            }

            catch
            {
            }
            return status1;

        }

        public static decimal lastissunot_approve()
        {
            SqlDataReader rdR;
            try
            {

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT MAX(Issue_ID) as lastid FROM Tbl_Iss_Request WHERE manger_status IS NULL";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    lastid = Convert.ToInt32(rdR["lastid"].ToString());

                }
                rdR.Close();
            }
            catch
            {
            }
            return lastid;
        }

    }
}


    
